package db;
import db.DBConnect;


import java.sql.CallableStatement;
//import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;


public class DBProOp extends DBConnect{
	protected int paraN;			//参数个数
	protected int runType;			//存储过程执行类型
	protected String callStr;			//调用语句
	protected String proName;				//存储过程名称
	protected ResultSet rs;				//结果集
	protected CallableStatement cStmt;			//CallableStatement对象
	/**
	 * @use 初始化存储过程操作类的相关参数
	 * @param tProName 存储过程名称
	 * @param runType 操作类型( 0为查询类型(executeQuery型), 1为更新类型(executeUpdate型) )
	 * @param tParaN 传入存储过程的参数个数
	 */
	protected void initDBOpPara( String tProName, int tParaN, int runType ){
		proName = tProName;
		paraN = tParaN;
	}
	
	/**
	 * @use 设定语法字符串细节
	 */
	protected void setCallStr(){
		callStr = "{call " + proName;
		callStr += "(";
		for ( int i = 0 ; i < paraN ; i ++ ){
			if ( i != paraN - 1 )
				callStr +="?,";
			else
				callStr += "?";
		}
		callStr += ")}";
	}
	
	/**
	 * @use 建立CallableStatement
	 */
	protected void openCStmt(){
		setCallStr();
		try{
			cStmt = conn.prepareCall( callStr );
			//conn.prepareStatement(callStr);
			//cStmt = conn.executeQuery();//executeQuery();
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 初始化 DBOp
	 * @param tProName 存储过程名称
	 * @param tRunType 操作类型( 0为查询类型(executeQuery型), 1为更新类型(executeUpdate型) )
	 * @param tParaN 传入存储过程的参数个数
	 */
	public void initDBOp( String tProName, int tParaN, int tRunType ){
		initDBOpPara( tProName, tParaN, tRunType );
		openCStmt();
	}
	
	/**
	 * @use 通过参数索引设定参数值(整型)
	 * @param paraIndex 参数索引
	 * @param paraValue 参数值
	 */
	public void setParaInt( int paraIndex, int paraValue ){
		try{
			cStmt.setInt( paraIndex, paraValue );
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 通过参数索引设定参数值(字符串)
	 * @param paraIndex 参数索引
	 * @param paraValue 参数值
	 */
	public void setParaString( int paraIndex, String paraValue ){
		try{
			cStmt.setString(paraIndex, paraValue);
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 通过参数索引设定参数值(日期)
	 * @param paraIndex 参数索引
	 * @param paraValue 参数值
	 */
	public void setParaDate( int paraIndex, Date paraValue ){
		try{
			cStmt.setDate( paraIndex , paraValue );
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 传递空值(整型)
	 * @param paraIndex 参数索引
	 */
	public void setParaNullInt( int paraIndex ){
		try{
			cStmt.setNull( paraIndex , Types.INTEGER );
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 传递空值(字符串)
	 * @param paraIndex 参数索引
	 */
	public void setParaNullString( int paraIndex ){
		try{
			cStmt.setNull( paraIndex , Types.VARCHAR );
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 传递空值(日期)
	 * @param paraIndex 参数索引
	 */
	public void setParaNullDate( int paraIndex ){
		try{
			cStmt.setNull( paraIndex , Types.DATE );
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 设置返回参数(整型)
	 * @param paraIndex 参数索引
	 */
	public void setRetInt( int paraIndex ){
		try{
			cStmt.registerOutParameter( paraIndex , Types.INTEGER);
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 设置返回参数(字符串)
	 * @param paraIndex 参数索引
	 */
	public void setRetString( int paraIndex ){
		try{
			cStmt.registerOutParameter( paraIndex , Types.VARCHAR);
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 获取返回参数(整型)
	 * @param paraIndex 参数索引
	 */
	public int getRetInt( int paraIndex ){
		int ret = -1;
		try{
			ret = cStmt.getInt( paraIndex );
		} catch ( SQLException e ){
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * @use 获取返回参数(字符串)
	 * @param paraIndex 参数索引
	 */
	public String getRetString( int paraIndex ){
		String ret = "null";
		try{
			ret = cStmt.getString( paraIndex );
		} catch ( SQLException e ){
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * @use 运行存储过程
	 */
	public void runPro(){
		try{
			if ( runType == 0 ){
					rs = cStmt.executeQuery();
			}
			else{
				cStmt.executeUpdate();
			}
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * 运行无查询结果的存储过程
	 */
	public void runProWithNoRS(){
		try{
			cStmt.execute();
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 关闭CallableStatement
	 */
	protected void closeCStmt(){
		try{
			cStmt.close();
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 关闭ResultSet
	 */
	protected void closeRS(){
		try{
			rs.close();
		} catch ( SQLException e ){
			e.printStackTrace();
		}
	}
	
	/**
	 * @use 关闭DBOp(包含关闭ResultSet,关闭CallableStatement)
	 */
	public void closeDBOp(){
		this.closeRS();
		this.closeCStmt();
	}

	/**
	 * 得到记录结果
	 * @return 返回记录集
	 */
	public ResultSet getRs() {
		return rs;
	}
}